from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

data_pack_inv_router = APIRouter(prefix="/container/packInv", tags=["容器库存管理"],
                                 dependencies=[Depends(oauth2_scheme)])


class PackInvSearch(PaginationBase):
    fsiteNo: str = Field(None, description="客户编号")
    fpackno: str = Field(None, description="容器编号")


@data_pack_inv_router.get("list", summary="容器库存查询接口")
def list_pack_inv(pack: PackInvSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_pack_inv a,i_ts_pack c"
    sql = sql + " where 1=1"
    sql = sql + " and a.fpackno=c.fpackno"
    if pack.fsiteNo is not None:
        sql = sql + " and a.fsite_no = :site_no "
        param["site_no"] = pack.fsiteNo
    if pack.fpackno is not None:
        sql = sql + " and a.fpackno like concat('%', :fpackno ,'%')"
        param["fpackno"] = pack.fpackno
    sql = sql + " order by  a.fsite_no,a.fpackno"
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fsite_no,a.fpackno,a.frfidbarcode,a.fgoodno,a.fbillno"
    sql = sql + ",a.fstate,a.fupddate,a.foperator,c.fpackname"
    sql = sql + " from i_pack_inv a,i_ts_pack c"
    sql = sql + " where 1=1"
    sql = sql + " and a.fpackno=c.fpackno"
    if pack.fsiteNo is not None:
        sql = sql + " and a.fsite_no = :site_no "
        param["site_no"] = pack.fsiteNo
    if pack.fpackno is not None:
        sql = sql + " and a.fpackno like concat('%', :fpackno ,'%')"
        param["fpackno"] = pack.fpackno
    sql = sql + " order by  a.fsite_no,a.fpackno"
    sql = sql + " limit :limit offset :offset "
    param["offset"] = pack.offset
    param["limit"] = pack.limit
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsiteno'] = data[key][0]
        d1['fpackno'] = data[key][1]
        d1['frfidbarcode'] = data[key][2]
        d1['fgoodno'] = data[key][3]
        d1['fbillno'] = data[key][4]
        d1['fstate'] = data[key][5]
        d1['fupddate'] = data[key][6]
        d1['foperator'] = data[key][7]
        d1['fpackname'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": pack.limit,
                 "page": pack.pageIndex,
                 "pages": (count + pack.limit - 1) // pack.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
